package com.softer.chengxin.excel.dao.provider;

import com.softer.chengxin.common.domin.Common;
import com.softer.chengxin.common.enums.StatisticalReportEnum;
import com.softer.chengxin.excel.domin.ExportExcel;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author Gong Zhiyang
 * @date 2019/2/27
 */
public class ReportDaoProvider {
    /**
     * 1查询企业数量
     *
     * @param map map
     */
    public String getCorpNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        sql.append("SELECT ds.name ,IFNULL(b.corpNum,0) as corpNum");
        sql.append("  FROM district_street ds");
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getCorpNum(sql, map, type);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询项目数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getProjectNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        sql.append("SELECT ds.name ");
        sql.append(",IFNULL(c.projectNum,0) as projectNum");
        sql.append("  FROM district_street ds");
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getProjectNum(sql, map, type);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询三体系认证数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getThreeSystemNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getThreeSystemNum(sql, map, type,param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询党支部数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getOrganizationNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getOrganizationNum(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询收入
     *
     * @param map 参数
     * @return SQL
     */
    public String getIncome(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getIncome(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询企业人数
     *
     * @param map 参数
     * @return SQL
     */
    public String getCorpPersonNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getCorpPersonNum(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询项目人数
     *
     * @param map 参数
     * @return SQL
     */
    public String getProjectPersonNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getProjectPersonNum(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询各个等级的数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getLevelNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getLevelNum(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询黑名单数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getBlackList(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getBlackList(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询项目各种面积
     *
     * @param map 参数
     * @return SQL
     */
    public String getProjectArea(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getProjectArea(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询老旧小区，智能化管理小区，保障住房数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getHouseNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getHouseNum(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询业委会数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getProprietor(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getProprietor(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询优良,负面信息数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getInfoNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getInfoNum(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }
    /**
     * 查询教育数量
     *
     * @param map 参数
     * @return SQL
     */
    public String getTrainNum(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String param = (String) map.get("param");
        commonTop(sql,param);
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        getTrainNum(sql, map, type, param);
        common(sql, roleId, area);
        return sql.toString();
    }

    private void getCorpNum(StringBuffer sql, HashMap<String, Object> map, String type) {
        sql.append(" LEFT JOIN ( SELECT ").append(type).append(", COUNT(*) AS corpNum ");
        sql.append(" FROM company_base_info c WHERE c.state IN (2, 9)");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") b ON b.").append(type).append(" = ds.id");
    }

    /**
     * 查询项目数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getProjectNum(StringBuffer sql, HashMap<String, Object> map, String type) {
        sql.append(" LEFT JOIN (select ").append(type).append(",COUNT(*) AS projectNum");
        sql.append(" FROM company_project c WHERE c.state in (2,9)");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询三体系认证数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getThreeSystemNum(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        sql.append(" LEFT JOIN (select ").append(type).append(", COUNT(*) as ").append(param).append(" FROM company_base_info c ");
        sql.append(" WHERE c.state in (2,9) and c.three_system=#{value}");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询党支部数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getOrganizationNum(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        sql.append(" LEFT JOIN (select ").append(type).append(", COUNT(*) as ").append(param).append(" FROM company_base_info c ");
        sql.append(" WHERE c.state in (2,9) and c.organization=#{value}");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询主营收入
     *
     * @param map 参数
     * @return SQL
     */
    public void getIncome(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        sql.append(" LEFT JOIN (SELECT ").append(type).append(",SUM(").append(param).append(") AS ").append(param);
        sql.append(" FROM company_base_info c WHERE c.state IN (2, 9)");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询企业人数
     *
     * @param map 参数
     * @return SQL
     */
    public void getCorpPersonNum(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_person p LEFT JOIN company_base_info c ON p.company_id=c.id WHERE p.person_id=0 and c.state in(2,9) ");
        if (StatisticalReportEnum.CORP_INSURANCE.getField().equals(param)) {
            sql.append(" and p.insurance=1");
        } else if (StatisticalReportEnum.CORP_CERTIFICATE.getField().equals(param)) {
            sql.append(" and p.certificate=1");
        }
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "p");
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询项目人数
     *
     * @param map 参数
     * @return SQL
     */
    public void getProjectPersonNum(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_person p LEFT JOIN company_project c ON p.project_id=c.id WHERE p.person_id<>0 and c.state=2 ");
        if (StatisticalReportEnum.PROJECT_INSURANCE.getField().equals(param)) {
            sql.append(" and p.insurance=1");
        } else if (StatisticalReportEnum.PROJECT_CERTIFICATE.getField().equals(param)) {
            sql.append(" and p.certificate=1");
        }
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "p");
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询各个等级的数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getLevelNum(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        ExportExcel exportExcel = (ExportExcel) map.get("exportExcel");
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM score_record s LEFT JOIN company_base_info c on s.poguid=c.id");
        sql.append(" WHERE s.dj=#{level} AND s.`year`=").append(exportExcel.getYear()).append(" and c.state in(2,9) ");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "s");
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询黑名单数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getBlackList(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_blacklist b LEFT JOIN company_base_info c on b.company_id = c.id WHERE b.state=1");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "b");
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询项目各种面积
     *
     * @param map 参数
     * @return SQL
     */
    public void getProjectArea(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        String parameter = (String) map.get("parameter");
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",SUM(").append(parameter).append(") AS ").append(param);
        sql.append(" FROM company_project c WHERE c.state=2 ");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询老旧小区，智能化管理小区，保障住房数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getHouseNum(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_project c WHERE c.state=2 ");
        if (StatisticalReportEnum.OLD_DISTRICT.getField().equals(param)) {
            sql.append(" and c.is_old=1");
        } else if (StatisticalReportEnum.INTELLIGENT.getField().equals(param)) {
            sql.append(" and c.is_intelligent=1");
        } else if (StatisticalReportEnum.INDEMNIFICATORY.getField().equals(param)) {
            sql.append(" and c.is_indemnificatory=1");
        }
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询业委会数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getProprietor(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_proprietor p LEFT JOIN company_project c ON p.project_id=c.project_id WHERE c.state=2 ");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "p");
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询优良,负面信息数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getInfoNum(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append("  FROM (SELECT (CASE WHEN d.project_id=0 THEN c. ").append(type).append(" ELSE p.").append(type).append(" end) ").append(" as ").append(type);
        sql.append(" FROM score_detail d ");
        sql.append(" LEFT JOIN company_project p ON d.project_id = p.id");
        sql.append(" LEFT JOIN company_base_info c ON d.corp_id = c.id");
        sql.append(" WHERE d.info_type=#{value} and d.state IN (2, 4, 5, 6, 7)");
        StatisticalReportDaoProvider.commonInfoSql(sql, map);
        sql.append(" )c GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询教育数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getTrainNum(StringBuffer sql, HashMap<String, Object> map, String type, String param) {
        String parameter = (String) map.get("parameter");
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_train t LEFT JOIN company_base_info c ON c.id=t.corp_id");
        sql.append(" WHERE c.state in(2,9)");
        if (StringUtils.isNotBlank(parameter)) {
            sql.append(" and t.").append(parameter).append("=#{value}");
        }
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "t");
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    private void commonTop(StringBuffer sql,String param){
        sql.append("SELECT ds.name ");
        sql.append(",IFNULL(c.").append(param).append(",0) as ").append(param);
        sql.append("  FROM district_street ds");
    }

    private void common(StringBuffer sql, Integer roleId, String area) {
        sql.append(" WHERE ");
        if (Common.THREE.equals(roleId)) {
            sql.append(" ds.parent_id = 0");
        } else {
            sql.append(" ds.parent_id <> 0");
        }
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and ds.id in (").append(area).append(")");
        }
    }
}
